#第十七章 访问数据
#python3.5没有mysql需要另外安装: pip3 install mysql-connector-python --allow-external mysql-connector-python
import mysql.connector

sqlUtils = mysql.connector

dbsql='''
CREATE DATABASE  `stu` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
'''



mkdbSql = '''
CREATE TABLE  `stu`.`grade` (
`stu_id` INT( 16 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '学生id',
`stu_name` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT  '0' COMMENT  '学生名',
`score` TINYINT( 3 ) UNSIGNED NOT NULL DEFAULT  '0' COMMENT  '学生成绩'
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT =  '学生表'
'''


#删除存在的数据库
dropDb = '''
DROP DATABASE IF EXISTS stu
'''


conn = mysql.connector.connect(user='root',password='',database='stu') #
cursor = conn.cursor()

#cursor.execute(dropDb)
#cursor.execute(dbsql);
#cursor.execute(mkdbSql)
#exit()

insertsql = "insert into stu_class(stu_id,stu_name,stu_score) values (%s,%s,%s)"

'''
for i in range(10):
	score = i*10-10;
	if score<=0: score=50
	ret = cursor.execute(insertsql,['null','zhou_'+str(i),str(score)])
	print(" cursor.rowcount=", cursor.rowcount,'ret=',ret)

conn.commit() #插入操作 这句很重要,不运行的话数据不能保存在数据库
'''

selectSql = "select stu_id,stu_name from stu_class where stu_id<=%s"
cursor.execute(selectSql,['10'])
data = cursor.fetchall()
print(cursor.description) #返回每一列的信息,比如字段名
print(data)

simpleRom = []
for i in range(len(data)):
	data[i]
	d = dict()
	for j in range(len(cursor.description)):
		dec = cursor.description[j]
		d[dec[0]] = data[i][j]
	simpleRom.append(d)
	
print(simpleRom) #得到一个字段名和值对应的数组[dict,dict,..]
	


cursor.close()
conn.close()

#SQLAlchemy python的ORM框架  pip3 install sqlalchemy



